<!DOCTYPE html>
<html lang="en">
    <head>
  <meta charset="utf-8" />
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <meta name="format-detection" content="telephone=no" />

  <title>
    28|案例篇--一个SQL查询要15秒，这是怎么回事？ | 迪克猪的博客
  </title>

  
  <link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png" />
  <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png" />
  <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png" />
  <link rel="manifest" href="/manifest.json" />
  <meta name="theme-color" content="#ffffff" />

  
  <link
    rel="stylesheet"
    href="https://unpkg.com/modern-normalize@0.6.0/modern-normalize.css"
  />

  
  
  
  
  <link rel="stylesheet" href="https://zsy619.github.io/style.min.f7761d111b74dd5c07f0111decee92938c12abc42e0fd319e1a07483e248b54e.css" integrity="sha256-93YdERt03VwH8BEd7O6Sk4wSq8QuD9MZ4aB0g&#43;JItU4=" />

  
  
    
  
</head>

    <body>
        <header id="header">
  <div class="header_container">
    <h1 class="sitetitle">
      <a href="https://zsy619.github.io" title="迪克猪的博客">迪克猪的博客</a>
    </h1>
    <nav class="navbar">
      <ul>
        <li><a href="https://zsy619.github.io">Home</a></li>
        
          <li>
            <a href="/post/">
              
              <span>LINUX性能优化</span>
            </a>
          </li>
        
          <li>
            <a href="/csapp/">
              
              <span>深入理解计算机系统_第三版</span>
            </a>
          </li>
        
          <li>
            <a href="/golang/">
              
              <span>golang</span>
            </a>
          </li>
        
          <li>
            <a href="/docker/">
              
              <span>容器</span>
            </a>
          </li>
        
          <li>
            <a href="/flutter/">
              
              <span>Flutter</span>
            </a>
          </li>
        
          <li>
            <a href="/know/">
              
              <span>知识点</span>
            </a>
          </li>
        
          <li>
            <a href="/categories/">
              
              <span>目录</span>
            </a>
          </li>
        
          <li>
            <a href="/about/">
              
              <span>关于</span>
            </a>
          </li>
        
        <li class="hide-sm"><a href="https://zsy619.github.io/index.xml" type="application/rss+xml">RSS</a></li>
      </ul>
    </nav>
  </div>
</header>

        
<section id="main">
  <article class="post content">
    <h2 class="title">28|案例篇--一个SQL查询要15秒，这是怎么回事？</h2>
    <div class="post_content">
      <p>运行下面的 docker exec 命令，进入 MySQL 的命令行界面：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-shell" data-lang="shell">
$ docker exec -i -t mysql mysql
...
​
Type <span style="color:#e6db74">&#39;help;&#39;</span> or <span style="color:#e6db74">&#39;\h&#39;</span> <span style="color:#66d9ef">for</span> help. Type <span style="color:#e6db74">&#39;\c&#39;</span> to clear the current input statement.
​
mysql&gt;

</code></pre></div><p>今天我们分析了一个商品搜索的应用程序。我们先是通过 top、iostat 分析了系统的 CPU 和磁盘使用情况，发现了磁盘的 I/O 瓶颈。接着，我们借助 pidstat ，发现瓶颈是 mysqld 导致的。
紧接着，我们又通过 strace、lsof，找出了 mysqld 正在读的文件。同时，根据文件的名字和路径，我们找出了 mysqld 正在操作的数据库和数据表。综合这些信息，我们判断，这是一个没有利用索引导致的慢查询问题。
于是，我们登录到 MySQL 命令行终端，用数据库分析工具进行验证，发现 MySQL 查询语句访问的字段，果然没有索引。所以，增加索引，就可以解决案例的性能问题了。</p>

    </div>
    <div class="info post_meta">
      <time datetime=2020-06-17T08:51:46&#43;0800 class="date">Wednesday, June 17, 2020</time>
      
        <ul class="tags">
        
          <li> <a href="https://zsy619.github.io/tags/linux%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96">linux性能优化</a> </li>
        
          <li> <a href="https://zsy619.github.io/tags/linux">linux</a> </li>
        
        </ul>
      
      
    </div>
    <div class="clearfix"></div>
  </article>
  
    <div class="other_posts">
      
      <a href="https://zsy619.github.io/post/27%E6%A1%88%E4%BE%8B%E7%AF%87%E4%B8%BA%E4%BB%80%E4%B9%88%E6%88%91%E7%9A%84%E7%A3%81%E7%9B%98io%E5%BB%B6%E8%BF%9F%E5%BE%88%E9%AB%98/" class="prev">27|案例篇--为什么我的磁盘I/O延迟很高？</a>
      
      
      <a href="https://zsy619.github.io/post/29%E6%A1%88%E4%BE%8B%E7%AF%87redis%E5%93%8D%E5%BA%94%E4%B8%A5%E9%87%8D%E5%BB%B6%E8%BF%9F%E5%A6%82%E4%BD%95%E8%A7%A3%E5%86%B3/" class="next">29|案例篇--Redis响应严重延迟，如何解决？</a>
      
    </div>
    <aside id="comments">
</aside>

  
</section>

        <a id="back_to_top" title="Go To Top" href="#">
  <span>
    <svg viewBox="0 0 24 24">
      <path fill="none" d="M0 0h24v24H0z"></path>
      <path d="M12 2L4.5 20.29l.71.71L12 18l6.79 3 .71-.71z"></path>
    </svg>
  </span>
</a>

        <footer id="footer">
  <p>
    <span>&copy; 2021 <a href="https://zsy619.github.io" title="迪克猪的博客">迪克猪的博客</a> </span>
    <span>Built with <a rel="nofollow" target="_blank" href="https://gohugo.io">Hugo</a></span>
    <span>Theme by <a rel="nofollow" target="_blank" href="https://github.com/wayjam/hugo-theme-mixedpaper">WayJam</a></span>
  </p>

  <script src="https://zsy619.github.io/js/main.min.8b182175f5874aeed0acc0979345c98d4bde22208ec4f36cc1d6e3102acb4b10.js" integrity="sha256-ixghdfWHSu7QrMCXk0XJjUveIiCOxPNswdbjECrLSxA=" crossorigin="anonymous" async></script>
</footer>

    </body>
</html>
